﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataProvider
    'Shared cnStr As String = "Server=OPENSUSE-PC\SQLEXPRESS; Database=QLDangKyHocPhan; Trusted_Connection=True"
    Shared cnStr As String = "Server=BAOPHUOC-PC\SQL; Database=QLDKHocPhan; Trusted_Connection=True"
    Private conn As SqlConnection
    Private cmd As SqlCommand
    Public Function DataProvider() As Boolean
        conn = New SqlConnection(cnStr)
        Try
            conn.Open()
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Không thể kết nối đến Database! " + ex.Message)
        End Try
        Return True
    End Function
    Public Sub Close()
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        conn.Dispose()
    End Sub
    Public Shared Function DocBang(ByVal chuoiLenh As String) As DataTable
        Dim bang As New DataTable()
        Dim TaoBang As New SqlDataAdapter(chuoiLenh, cnStr)
        TaoBang.FillSchema(bang, SchemaType.Mapped)
        TaoBang.Fill(bang)
        Return bang
    End Function

    Public Shared Function DocCauTruc(ByVal chuoiLenh As String) As DataTable
        Dim bang As New DataTable()
        Dim TaoBang As New SqlDataAdapter(chuoiLenh, cnStr)
        TaoBang.FillSchema(bang, SchemaType.Mapped)
        Return bang
    End Function

    Public Shared Sub GhiBang(ByVal bang As DataTable, ByVal tenBang As String)
        Dim TaoBang As New SqlDataAdapter("SELECT * FROM " & tenBang, cnStr)
        Dim boXayDung As New SqlCommandBuilder(TaoBang)
        TaoBang.Update(bang)
    End Sub

#Region "Executequery"
    Public Function Excute(ByVal sSql As String)
        cmd = New SqlCommand()
        DataProvider()
        cmd.Connection = conn
        cmd.CommandText = sSql
        Return cmd.ExecuteNonQuery()
    End Function
    Public Shared Function Executequery(ByVal sp_name As String) As DataTable
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        Dim da As SqlDataAdapter = New SqlDataAdapter(sp_name, cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt
    End Function
    Public Shared Function ExecutequeryNotStore(ByVal sp_name As String) As DataTable
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        cn.Open()

        Dim da As SqlDataAdapter = New SqlDataAdapter(sp_name, cn)

        da.SelectCommand.ExecuteNonQuery()
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt
    End Function

    Public Shared Function Executequery(ByVal sp_name As String, ByVal Para As SqlParameter) As DataTable
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        Dim da As SqlDataAdapter = New SqlDataAdapter(sp_name, cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        da.SelectCommand.Parameters.Add(Para)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt
    End Function

    Public Shared Function Executequery(ByVal sp_name As String, ByVal lstPara As List(Of SqlParameter)) As DataTable
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        Dim da As SqlDataAdapter = New SqlDataAdapter(sp_name, cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        da.SelectCommand.Parameters.AddRange(lstPara.ToArray())
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt
    End Function
#End Region
#Region "ExecuteNonquery"
    Public Shared Sub ExecuteNonquery(ByVal sp_name As String)
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        Dim cmn As SqlCommand = New SqlCommand(sp_name, cn)
        cmn.CommandType = CommandType.StoredProcedure
        cmn.Connection.Open()
        cmn.ExecuteNonQuery()
        cmn.Connection.Close()
    End Sub

    Public Shared Sub ExecuteNonquery(ByVal sp_name As String, ByVal Para As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        Dim cmn As SqlCommand = New SqlCommand(sp_name, cn)
        cmn.Parameters.Add(Para)
        cmn.Connection.Open()
        cmn.ExecuteNonQuery()
        cmn.Connection.Close()
    End Sub

    Public Shared Sub ExecuteNonquery(ByVal sp_name As String, ByVal lstPara As List(Of SqlParameter))
        Dim cn As SqlConnection = New SqlConnection(cnStr)
        Dim cmn As SqlCommand = New SqlCommand(sp_name, cn)
        cmn.Parameters.Add(lstPara.ToArray())
        cmn.Connection.Open()
        cmn.ExecuteNonQuery()
        cmn.Connection.Close()
    End Sub

    Public Shared Function GetConnection() As SqlConnection
        Dim cn As SqlConnection = New SqlConnection(cnStr)

        Return cn
    End Function
#End Region

    Public Function GetData(ByVal sSql As String) As DataTable
        Dim dt As New DataTable
        DataProvider()
        cmd = New SqlCommand(sSql, conn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(dt)
        Close()
        Return dt
    End Function
    Function GetDataGV(ByVal p1 As String) As DataTable
        Throw New NotImplementedException
    End Function

End Class
